-- OpenManus 数据库初始化脚本

-- 创建 PGVector 扩展
CREATE EXTENSION IF NOT EXISTS vector;

-- 创建智能体表（MyBatis-Plus 风格）
CREATE TABLE IF NOT EXISTS t_agents (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    agent_type VARCHAR(100) NOT NULL,
    state VARCHAR(50) DEFAULT 'IDLE',
    config JSONB,
    system_prompt TEXT,
    next_step_prompt TEXT,
    max_steps INTEGER DEFAULT 10,
    timeout_seconds INTEGER DEFAULT 300,
    enabled BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted INTEGER DEFAULT 0,
    version INTEGER DEFAULT 1
);

-- 创建记忆表
CREATE TABLE IF NOT EXISTS memories (
    id BIGSERIAL PRIMARY KEY,
    key VARCHAR(500) NOT NULL UNIQUE,
    content TEXT NOT NULL,
    memory_type VARCHAR(50) NOT NULL,
    importance DECIMAL(3,2) DEFAULT 0.5,
    access_count INTEGER DEFAULT 0,
    embedding vector(1536),
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_accessed_at TIMESTAMP,
    last_updated_at TIMESTAMP,
    expires_at TIMESTAMP
);

-- 创建任务执行记录表
CREATE TABLE IF NOT EXISTS task_executions (
    id BIGSERIAL PRIMARY KEY,
    agent_name VARCHAR(255) NOT NULL,
    request TEXT NOT NULL,
    status VARCHAR(50) NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP,
    total_steps INTEGER DEFAULT 0,
    result TEXT,
    error_message TEXT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建工具使用记录表
CREATE TABLE IF NOT EXISTS tool_usages (
    id BIGSERIAL PRIMARY KEY,
    task_execution_id BIGINT REFERENCES task_executions(id),
    tool_name VARCHAR(255) NOT NULL,
    parameters JSONB,
    result TEXT,
    success BOOLEAN NOT NULL,
    execution_time_ms BIGINT,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建系统配置表
CREATE TABLE IF NOT EXISTS system_configs (
    id BIGSERIAL PRIMARY KEY,
    config_key VARCHAR(255) NOT NULL UNIQUE,
    config_value TEXT NOT NULL,
    description TEXT,
    config_type VARCHAR(50) DEFAULT 'STRING',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_agents_name ON t_agents(name);
CREATE INDEX IF NOT EXISTS idx_agents_type ON t_agents(agent_type);
CREATE INDEX IF NOT EXISTS idx_agents_state ON t_agents(state);
CREATE INDEX IF NOT EXISTS idx_agents_enabled ON t_agents(enabled);
CREATE INDEX IF NOT EXISTS idx_agents_deleted ON t_agents(deleted);

CREATE INDEX IF NOT EXISTS idx_memories_key ON memories(key);
CREATE INDEX IF NOT EXISTS idx_memories_type ON memories(memory_type);
CREATE INDEX IF NOT EXISTS idx_memories_importance ON memories(importance);
CREATE INDEX IF NOT EXISTS idx_memories_created_at ON memories(created_at);
CREATE INDEX IF NOT EXISTS idx_memories_expires_at ON memories(expires_at);

-- 为向量相似性搜索创建索引
CREATE INDEX IF NOT EXISTS idx_memories_embedding ON memories USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

CREATE INDEX IF NOT EXISTS idx_task_executions_agent ON task_executions(agent_name);
CREATE INDEX IF NOT EXISTS idx_task_executions_status ON task_executions(status);
CREATE INDEX IF NOT EXISTS idx_task_executions_start_time ON task_executions(start_time);

CREATE INDEX IF NOT EXISTS idx_tool_usages_task_id ON tool_usages(task_execution_id);
CREATE INDEX IF NOT EXISTS idx_tool_usages_tool_name ON tool_usages(tool_name);
CREATE INDEX IF NOT EXISTS idx_tool_usages_success ON tool_usages(success);

CREATE INDEX IF NOT EXISTS idx_system_configs_key ON system_configs(config_key);

-- 插入默认配置
INSERT INTO system_configs (config_key, config_value, description, config_type) VALUES
    ('system.version', '1.0.0', 'OpenManus 系统版本', 'STRING'),
    ('agent.max_steps', '20', '智能体最大执行步数', 'INTEGER'),
    ('agent.default_timeout', '300', '智能体默认超时时间（秒）', 'INTEGER'),
    ('memory.max_items', '10000', '最大记忆条目数', 'INTEGER'),
    ('memory.cleanup_interval', '3600', '记忆清理间隔（秒）', 'INTEGER'),
    ('tools.python.enabled', 'true', '是否启用 Python 工具', 'BOOLEAN'),
    ('tools.browser.enabled', 'true', '是否启用浏览器工具', 'BOOLEAN'),
    ('tools.search.enabled', 'true', '是否启用搜索工具', 'BOOLEAN')
ON CONFLICT (config_key) DO NOTHING;

-- 创建更新时间戳的函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为需要的表创建自动更新时间戳的触发器
CREATE TRIGGER update_agents_updated_at BEFORE UPDATE ON agents
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_system_configs_updated_at BEFORE UPDATE ON system_configs
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建记忆清理函数
CREATE OR REPLACE FUNCTION cleanup_expired_memories()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM memories WHERE expires_at IS NOT NULL AND expires_at < CURRENT_TIMESTAMP;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- 创建记忆相似性搜索函数
CREATE OR REPLACE FUNCTION search_similar_memories(
    query_embedding vector(1536),
    similarity_threshold DECIMAL DEFAULT 0.7,
    max_results INTEGER DEFAULT 10
)
RETURNS TABLE(
    id BIGINT,
    key VARCHAR(500),
    content TEXT,
    similarity DECIMAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        m.id,
        m.key,
        m.content,
        (1 - (m.embedding <=> query_embedding))::DECIMAL as similarity
    FROM memories m
    WHERE m.embedding IS NOT NULL
        AND (1 - (m.embedding <=> query_embedding)) >= similarity_threshold
        AND (m.expires_at IS NULL OR m.expires_at > CURRENT_TIMESTAMP)
    ORDER BY m.embedding <=> query_embedding
    LIMIT max_results;
END;
$$ LANGUAGE plpgsql;

-- 创建任务执行统计视图
CREATE OR REPLACE VIEW task_execution_stats AS
SELECT 
    agent_name,
    COUNT(*) as total_executions,
    COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) as successful_executions,
    COUNT(CASE WHEN status = 'ERROR' THEN 1 END) as failed_executions,
    AVG(EXTRACT(EPOCH FROM (end_time - start_time))) as avg_execution_time_seconds,
    AVG(total_steps) as avg_steps
FROM task_executions
WHERE end_time IS NOT NULL
GROUP BY agent_name;

-- 创建工具使用统计视图
CREATE OR REPLACE VIEW tool_usage_stats AS
SELECT 
    tool_name,
    COUNT(*) as total_usages,
    COUNT(CASE WHEN success THEN 1 END) as successful_usages,
    COUNT(CASE WHEN NOT success THEN 1 END) as failed_usages,
    AVG(execution_time_ms) as avg_execution_time_ms,
    DATE_TRUNC('day', created_at) as usage_date
FROM tool_usages
GROUP BY tool_name, DATE_TRUNC('day', created_at)
ORDER BY usage_date DESC, tool_name;

COMMENT ON DATABASE openmanus IS 'OpenManus Java 智能体框架数据库';
COMMENT ON TABLE agents IS '智能体注册表';
COMMENT ON TABLE memories IS '智能体记忆存储表';
COMMENT ON TABLE task_executions IS '任务执行记录表';
COMMENT ON TABLE tool_usages IS '工具使用记录表';
COMMENT ON TABLE system_configs IS '系统配置表';
